{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 122
    },
    "colab_type": "code",
    "collapsed": false,
    "executionInfo": {
     "elapsed": 8781,
     "status": "ok",
     "timestamp": 1546332696219,
     "user": {
      "displayName": "Cheng Gavin",
      "photoUrl": "",
      "userId": "16557339152332771497"
     },
     "user_tz": -480
    },
    "id": "Xfrg3qJ857zS",
    "outputId": "d1b9f86f-38b2-4cd0-c8af-8278faea271b"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting pymysql\n",
      "\u001b[?25l  Downloading https://files.pythonhosted.org/packages/ed/39/15045ae46f2a123019aa968dfcba0396c161c20f855f11dea6796bcaae95/PyMySQL-0.9.3-py2.py3-none-any.whl (47kB)\n",
      "\u001b[K    100% |████████████████████████████████| 51kB 4.5MB/s \n",
      "\u001b[?25hInstalling collected packages: pymysql\n",
      "Successfully installed pymysql-0.9.3\n"
     ]
    }
   ],
   "source": [
    "!pip install pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 153
    },
    "colab_type": "code",
    "collapsed": false,
    "executionInfo": {
     "elapsed": 23853,
     "status": "ok",
     "timestamp": 1546332711321,
     "user": {
      "displayName": "Cheng Gavin",
      "photoUrl": "",
      "userId": "16557339152332771497"
     },
     "user_tz": -480
    },
    "id": "WXKEkEzink_m",
    "outputId": "ad443c6b-d381-468b-f516-25dae1f990e0"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting git+https://github.com/coreylynch/pyFM\n",
      "  Cloning https://github.com/coreylynch/pyFM to /tmp/pip-req-build-ixrclqal\n",
      "Building wheels for collected packages: pyfm\n",
      "  Running setup.py bdist_wheel for pyfm ... \u001b[?25l-\b \b\\\b \b|\b \b/\b \b-\b \b\\\b \bdone\n",
      "\u001b[?25h  Stored in directory: /tmp/pip-ephem-wheel-cache-g1vyicl5/wheels/3b/d9/ef/1b148c527d39344632833679e79b3db1798a40b0f64f917b13\n",
      "Successfully built pyfm\n",
      "Installing collected packages: pyfm\n",
      "Successfully installed pyfm-0.0.0\n"
     ]
    }
   ],
   "source": [
    "!pip install git+https://github.com/coreylynch/pyFM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "Qr4yc61W57Ic"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pymysql\n",
    "import pymysql.cursors\n",
    "from functools import reduce\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import uuid\n",
    "import datetime\n",
    "from pyfm import pylibfm\n",
    "from sklearn.feature_extraction import DictVectorizer\n",
    "from sklearn.metrics.pairwise import pairwise_distances\n",
    "np.set_printoptions(precision=3)\n",
    "np.set_printoptions(suppress=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "4Rdfk--W5-BA"
   },
   "outputs": [],
   "source": [
    "def get_connection():\n",
    "    return pymysql.connect(host='rm-2zeqqm6994abi7b6dqo.mysql.rds.aliyuncs.com',\n",
    "                               user='root',\n",
    "                               password='demo12DB',\n",
    "                               db='recsys',\n",
    "                               port=3306,\n",
    "                               charset ='utf8',\n",
    "                               use_unicode=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "85xHY1TA6MnC"
   },
   "outputs": [],
   "source": [
    "df = pd.read_sql_query(\"select * from movie\", get_connection())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "Wnd8Gwsk6tLv"
   },
   "outputs": [],
   "source": [
    "#df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "e4lQur0O67k0"
   },
   "outputs": [],
   "source": [
    "#df_exp = df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "CcJVn59xWoDT"
   },
   "outputs": [],
   "source": [
    "#df_exp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "0TEiUd1MeqUp"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "nLE09L4bgrwg"
   },
   "outputs": [],
   "source": [
    "def get_dim_dict(df, dim_name):\n",
    "  type_list = list(map(lambda x:x.split('|') ,df[dim_name]))\n",
    "  type_list = [x for l in type_list for x in l]\n",
    "  def reduce_func(x, y):\n",
    "    for i in x:\n",
    "      if i[0] == y[0][0]:\n",
    "        x.remove(i)\n",
    "        x.append(((i[0],i[1] + 1)))\n",
    "        return x\n",
    "    x.append(y[0])\n",
    "    return x\n",
    "  l = filter(lambda x:x != None, map(lambda x:[(x, 1)], type_list))\n",
    "  type_zip = reduce(reduce_func, list(l))\n",
    "  #type_list = sorted(list(set(type_list)))\n",
    "  #type_zip = zip(list(range(len(type_list))), type_list)\n",
    "  #print(len(type_zip))\n",
    "  #print(type_zip)\n",
    "  type_dict = {}\n",
    "  for i in type_zip:\n",
    "    type_dict[i[0]] = i[1]\n",
    "  return type_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "NgCL3BIGjYua"
   },
   "outputs": [],
   "source": [
    "type_dict = get_dim_dict(df, 'type')\n",
    "actors_dict = get_dim_dict(df, 'actors')\n",
    "director_dict = get_dim_dict(df, 'director')\n",
    "trait_dict = get_dim_dict(df, 'trait')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "aKGvytP_lSOe"
   },
   "outputs": [],
   "source": [
    "#print(type_dict)\n",
    "#print(actors_dict)\n",
    "#print(director_dict)\n",
    "#print(trait_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "Zauqzs6v8r7h"
   },
   "outputs": [],
   "source": [
    "#director_dict['吉姆·弗尔']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "vG6UtDPM8zCw"
   },
   "outputs": [],
   "source": [
    "#actors_dict['马克·莱昂纳蒂']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "DlYjtQt3mcUM"
   },
   "outputs": [],
   "source": [
    "df_ = df.drop(['ADD_TIME', 'enable', 'rat', 'id', 'name'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "xuCi-7VAuBkW"
   },
   "outputs": [],
   "source": [
    "#df_.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "Y2sXwXAhz1FA"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "jicjc-23oFRO"
   },
   "outputs": [],
   "source": [
    "movie_dict_list = []\n",
    "for i in df_.index:\n",
    "  movie_dict = {}\n",
    "  #type\n",
    "  for s_type in df_.iloc[i]['type'].split('|'):\n",
    "    movie_dict[s_type] = 1\n",
    "  #actors\n",
    "  for s_actor in df_.iloc[i]['actors'].split('|'):\n",
    "    if actors_dict[s_actor] < 2:\n",
    "      movie_dict['other_actor'] = 1\n",
    "    else:\n",
    "      movie_dict[s_actor] = 1\n",
    "  #regios\n",
    "  movie_dict[df_.iloc[i]['region']] = 1\n",
    "  #director\n",
    "  for s_director in df_.iloc[i]['director'].split('|'):\n",
    "    if director_dict[s_director] < 2:\n",
    "      movie_dict['other_director'] = 1\n",
    "    else:\n",
    "      movie_dict[s_director] = 1\n",
    "  #trait\n",
    "  for s_trait in df_.iloc[i]['trait'].split('|'):\n",
    "    movie_dict[s_trait] = 1\n",
    "  movie_dict_list.append(movie_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "DwakpcbVvGmu"
   },
   "outputs": [],
   "source": [
    "v = DictVectorizer()\n",
    "X = v.fit_transform(movie_dict_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "collapsed": false,
    "executionInfo": {
     "elapsed": 62701,
     "status": "ok",
     "timestamp": 1546332793171,
     "user": {
      "displayName": "Cheng Gavin",
      "photoUrl": "",
      "userId": "16557339152332771497"
     },
     "user_tz": -480
    },
    "id": "rhb2GHdMwJ4l",
    "outputId": "3e8425e0-520e-4953-d20a-982c3a47e670"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6630, 5858)"
      ]
     },
     "execution_count": 19,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 340
    },
    "colab_type": "code",
    "collapsed": false,
    "executionInfo": {
     "elapsed": 61858,
     "status": "ok",
     "timestamp": 1546332793172,
     "user": {
      "displayName": "Cheng Gavin",
      "photoUrl": "",
      "userId": "16557339152332771497"
     },
     "user_tz": -480
    },
    "id": "Od99RGJ96ujH",
    "outputId": "35158a94-3ef1-4149-f9be-75e1a1fd855f"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'other_actor': 1,\n",
       "  '冒险': 1,\n",
       "  '动作': 1,\n",
       "  '奇幻': 1,\n",
       "  '妮可·基德曼': 1,\n",
       "  '威廉·达福': 1,\n",
       "  '娜塔莉亚姗福瑞': 1,\n",
       "  '帕特里克·威尔森': 1,\n",
       "  '朱莉·安德鲁斯': 1,\n",
       "  '杜夫·龙格尔': 1,\n",
       "  '杰曼·翰苏': 1,\n",
       "  '杰森·莫玛': 1,\n",
       "  '格拉汉姆·麦克泰维什': 1,\n",
       "  '温子仁': 1,\n",
       "  '特穆拉·莫里森': 1,\n",
       "  '约翰·瑞斯-戴维斯': 1,\n",
       "  '美国': 1,\n",
       "  '艾梅柏·希尔德': 1,\n",
       "  '魔幻': 1}]"
      ]
     },
     "execution_count": 20,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_dict_list[0:1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "LcE1Ir5HSk-_"
   },
   "outputs": [],
   "source": [
    "#pd.DataFrame(data=X.toarray(), columns=v.feature_names_).T.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "5L8l65lg7AoE"
   },
   "outputs": [],
   "source": [
    "item_similarity = pairwise_distances(X, metric='cosine')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "YEEu1ZhQVL6P"
   },
   "outputs": [],
   "source": [
    "#item_similarity.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "collapsed": false,
    "executionInfo": {
     "elapsed": 39744,
     "status": "ok",
     "timestamp": 1546332795577,
     "user": {
      "displayName": "Cheng Gavin",
      "photoUrl": "",
      "userId": "16557339152332771497"
     },
     "user_tz": -480
    },
    "id": "UUcGZ5FMVQSm",
    "outputId": "528c2e27-d84d-4469-b636-2a1d26d04a4c"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1707 0.4777670321329065\n"
     ]
    }
   ],
   "source": [
    "compare_index = 3\n",
    "index = 0\n",
    "_max_index = 0\n",
    "_max = 1\n",
    "for i in item_similarity[compare_index]:\n",
    "  if i < _max and i != 0:\n",
    "    _max = i\n",
    "    _max_index = index\n",
    "  index = index + 1\n",
    "print(_max_index, _max)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "S06VsY4cFgA6"
   },
   "outputs": [],
   "source": [
    "index_of_sim = _max_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 323
    },
    "colab_type": "code",
    "collapsed": false,
    "executionInfo": {
     "elapsed": 36440,
     "status": "ok",
     "timestamp": 1546332795580,
     "user": {
      "displayName": "Cheng Gavin",
      "photoUrl": "",
      "userId": "16557339152332771497"
     },
     "user_tz": -480
    },
    "id": "LMP8ylRWVMqn",
    "outputId": "0519a94f-a6bc-4c2f-a66b-98fb87c72e3e"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               0    1\n",
      "Lady Gaga    1.0  0.0\n",
      "other_actor  1.0  1.0\n",
      "俄罗斯          1.0  1.0\n",
      "剧情           0.0  1.0\n",
      "动作           1.0  1.0\n",
      "喜剧           1.0  0.0\n",
      "奥列格·扬科夫斯基    0.0  1.0\n",
      "女性           0.0  1.0\n",
      "安东尼·威勒       0.0  1.0\n",
      "悬疑           0.0  1.0\n",
      "惊悚           1.0  1.0\n",
      "搞笑           1.0  1.0\n",
      "犯罪           1.0  1.0\n",
      "米歇尔·罗德里格兹    1.0  0.0\n",
      "索菲娅·维加拉      1.0  0.0\n",
      "罗伯特·罗德里格兹    1.0  0.0\n",
      "艾梅柏·希尔德      1.0  0.0\n"
     ]
    }
   ],
   "source": [
    "df.iloc[index_of_sim]\n",
    "movie_dict_list[index_of_sim]\n",
    "df_106 = pd.DataFrame(data=X.todense()[index_of_sim], columns=v.feature_names_)\n",
    "df_0 = pd.DataFrame(data=X.todense()[compare_index], columns=v.feature_names_)\n",
    "df_diff = pd.concat([df_0, df_106], axis=0, ignore_index=True)\n",
    "#df_diff.reindex(['0','1'])\n",
    "df_diff = df_diff.T\n",
    "print(df_diff[(df_diff[0] != 0) | (df_diff[1] != 0)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "HZVO4bHXyHEU"
   },
   "outputs": [],
   "source": [
    "#df.iloc[index_of_sim]\n",
    "#movie_dict_list[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "4jWBmBMczT1O"
   },
   "outputs": [],
   "source": [
    "#df.iloc[compare_index]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "7TzGxhlozd6O"
   },
   "outputs": [],
   "source": [
    "#item_similarity.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "75SRFh7lZEqh"
   },
   "outputs": [],
   "source": [
    "# li_dict = {1:0.1, 2:0.2, 5:0.4, 8:0.3}\n",
    "# min(li_dict.values())\n",
    "# dict(filter(lambda x:x[1] != 0.4 ,li_dict.items()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "wpWxp2eNbn3C"
   },
   "outputs": [],
   "source": [
    "df_sim = pd.DataFrame(data=item_similarity)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "collapsed": false,
    "executionInfo": {
     "elapsed": 572,
     "status": "ok",
     "timestamp": 1546333169874,
     "user": {
      "displayName": "Cheng Gavin",
      "photoUrl": "",
      "userId": "16557339152332771497"
     },
     "user_tz": -480
    },
    "id": "0XP9EY9No5dy",
    "outputId": "fc47b4b5-c7b7-4ce5-f9a5-0d7aecb0bbc7"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6630"
      ]
     },
     "execution_count": 43,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item_similarity.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 54
    },
    "colab_type": "code",
    "collapsed": false,
    "executionInfo": {
     "elapsed": 2595,
     "status": "ok",
     "timestamp": 1546333490464,
     "user": {
      "displayName": "Cheng Gavin",
      "photoUrl": "",
      "userId": "16557339152332771497"
     },
     "user_tz": -480
    },
    "id": "eNh5z-MznwHC",
    "outputId": "108dca6f-9b22-43dc-ef93-e8d99fe91eb8"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_items([(6600, 0.0), (2463, 0.36099034957730636), (405, 0.37720084467078163), (2796, 0.37720084467078163), (4625, 0.37720084467078163), (252, 0.39753592392329073), (3218, 0.40000000000000013), (988, 0.40371520600005606), (3605, 0.40371520600005606), (1706, 0.41445995623088017), (2691, 0.41905249806888767), (3364, 0.41905249806888767), (5731, 0.41905249806888767), (6087, 0.41905249806888767), (2540, 0.42710810076845374), (4116, 0.4284523933505918), (3661, 0.4363981380233656), (782, 0.4479475525261166), (2098, 0.4479475525261166), (3456, 0.4479475525261166), (5634, 0.4479475525261166), (3784, 0.45505073908693394), (4415, 0.45505073908693394), (654, 0.4666666666666668), (2100, 0.4666666666666668), (3660, 0.4666666666666668), (6255, 0.4666666666666668), (835, 0.47825080525004904), (1469, 0.47825080525004904), (2742, 0.47825080525004904), (2789, 0.47825080525004904), (2842, 0.47825080525004904), (3151, 0.47825080525004904), (3259, 0.47825080525004904), (3503, 0.47825080525004904), (4624, 0.47825080525004904), (5128, 0.47825080525004904), (6512, 0.47825080525004904), (3199, 0.4836022205056778), (4879, 0.4836022205056778), (5363, 0.4836022205056778), (5468, 0.4836022205056778), (6463, 0.4836022205056778), (1063, 0.4836022205056779), (2129, 0.4836022205056779), (20, 0.49871958817239703), (843, 0.49871958817239703), (1031, 0.49871958817239703), (1725, 0.49871958817239703), (2743, 0.49871958817239703), (3721, 0.49871958817239703), (3754, 0.49871958817239703), (4233, 0.49871958817239703), (4500, 0.49871958817239703), (5236, 0.49871958817239703), (5790, 0.4990205671318805), (6066, 0.4990205671318805), (6337, 0.4990205671318805), (404, 0.5101020514433645), (1161, 0.5101020514433645), (1659, 0.5101020514433645), (2358, 0.5101020514433645), (2455, 0.5101020514433645), (2733, 0.5101020514433645), (3058, 0.5101020514433645), (3388, 0.5101020514433645), (3685, 0.5101020514433645), (4426, 0.5101020514433645), (4877, 0.5101020514433645), (4995, 0.5101020514433645), (2311, 0.5120499635257335), (3819, 0.5131355044398522), (5024, 0.5131355044398522), (5768, 0.5131355044398522), (1749, 0.516954108460352), (1807, 0.516954108460352), (2068, 0.516954108460352), (4009, 0.516954108460352), (4420, 0.516954108460352), (4486, 0.516954108460352), (4510, 0.516954108460352), (4736, 0.516954108460352), (4740, 0.516954108460352), (4832, 0.516954108460352), (5019, 0.516954108460352), (5195, 0.516954108460352), (5568, 0.516954108460352), (6526, 0.516954108460352), (342, 0.5329006335030861), (1460, 0.5329006335030861), (1709, 0.5329006335030861), (2185, 0.5329006335030861), (2695, 0.5329006335030861), (2943, 0.5329006335030861), (3038, 0.5329006335030861), (3373, 0.5329006335030861), (3481, 0.5329006335030861), (3550, 0.5329006335030861), (4105, 0.5329006335030861), (4739, 0.5329006335030861), (4937, 0.5329006335030861), (295, 0.5333333333333334), (559, 0.5333333333333334), (1014, 0.5333333333333334), (1270, 0.5333333333333334), (1782, 0.5333333333333334), (2084, 0.5333333333333334), (2483, 0.5333333333333334), (2572, 0.5333333333333334), (3145, 0.5333333333333334), (3158, 0.5333333333333334), (3188, 0.5333333333333334), (3489, 0.5333333333333334), (3539, 0.5333333333333334), (3932, 0.5333333333333334), (4466, 0.5333333333333334), (4862, 0.5333333333333334), (4909, 0.5333333333333334), (5585, 0.5333333333333334), (5812, 0.5333333333333334), (6482, 0.5333333333333334), (1923, 0.5435645354123617), (2675, 0.5435645354123617), (3700, 0.5435645354123617), (4080, 0.5435645354123617), (5831, 0.5435645354123617), (5898, 0.5435645354123617), (5917, 0.5435645354123617), (6376, 0.5435645354123617), (768, 0.5481519429424682), (771, 0.5481519429424682), (3787, 0.5481519429424682), (5329, 0.5481519429424682), (5928, 0.5481519429424682), (500, 0.5527864045000421), (817, 0.5527864045000421), (1226, 0.5527864045000421), (1554, 0.5527864045000421), (1568, 0.5527864045000421), (1578, 0.5527864045000421), (2069, 0.5527864045000421), (2325, 0.5527864045000421), (2388, 0.5527864045000421), (2492, 0.5527864045000421), (2938, 0.5527864045000421), (3346, 0.5527864045000421), (3380, 0.5527864045000421), (3594, 0.5527864045000421), (3777, 0.5527864045000421), (4093, 0.5527864045000421), (4236, 0.5527864045000421), (4431, 0.5527864045000421), (4724, 0.5527864045000421), (4813, 0.5527864045000421), (4932, 0.5527864045000421), (5530, 0.5527864045000421), (41, 0.5616429962403954), (2103, 0.5616429962403954), (2932, 0.5616429962403954), (5479, 0.5616429962403954), (5543, 0.5616429962403954), (6, 0.5696685170880649), (905, 0.5696685170880649), (1562, 0.5696685170880649), (2427, 0.5696685170880649), (2504, 0.5696685170880649), (2736, 0.5696685170880649), (2887, 0.5696685170880649), (3467, 0.5696685170880649), (3844, 0.5696685170880649), (4541, 0.5696685170880649), (4608, 0.5696685170880649), (4718, 0.5696685170880649), (5286, 0.5696685170880649), (5375, 0.5696685170880649), (5749, 0.5696685170880649), (48, 0.5703310755763402), (166, 0.5703310755763402), (302, 0.5703310755763402), (538, 0.5703310755763402), (693, 0.5703310755763402), (754, 0.5703310755763402), (1074, 0.5703310755763402), (1531, 0.5703310755763402), (1610, 0.5703310755763402), (1773, 0.5703310755763402), (2104, 0.5703310755763402), (2146, 0.5703310755763402), (2663, 0.5703310755763402), (2811, 0.5703310755763402), (2917, 0.5703310755763402), (2998, 0.5703310755763402), (3007, 0.5703310755763402), (3144, 0.5703310755763402), (3255, 0.5703310755763402), (3310, 0.5703310755763402), (3736, 0.5703310755763402), (3816, 0.5703310755763402), (3842, 0.5703310755763402), (4003, 0.5703310755763402)])"
      ]
     },
     "execution_count": 49,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sim.nsmallest(200, 6600)[6600].to_dict().items()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "ibs6QhZ-c_32"
   },
   "outputs": [],
   "source": [
    "#df_sim.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "V54pDt7Io2gQ"
   },
   "outputs": [],
   "source": [
    "def insert_one_ibmovie(id_,\n",
    "                      movieid,\n",
    "                      recmovieid,\n",
    "                      recmovierat,\n",
    "                      simrat,\n",
    "                      time,\n",
    "                      enable, connection):\n",
    "  sql = 'insert into ibmovie (id, movieid, recmovieid, recmovierat, simrat, time, enable) values (\\'%s\\',\\'%s\\',\\'%s\\',\\'%s\\',\\'%s\\',\\'%s\\',\\'%s\\')' % (id_, movieid, recmovieid, recmovierat, simrat, time, enable)\n",
    "  #print(sql)\n",
    "  try:\n",
    "    with connection.cursor() as cursor:\n",
    "        cout=cursor.execute(sql)\n",
    "  except Exception as e:\n",
    "    print('exception:'+str(e))\n",
    "    try:\n",
    "      connection.close()\n",
    "    except:\n",
    "      pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "6rZZt9yLuclQ"
   },
   "source": [
    "## Test code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "ztxAOQqIubhc"
   },
   "outputs": [],
   "source": [
    "# _conn = get_connection()\n",
    "# insert_one_ibmovie(uuid.uuid4(), '1', '2', '6', '0.1', datetime.datetime.now(), '1', _conn)\n",
    "# _conn.commit()\n",
    "# _conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "wqxqYx-uv0Xt"
   },
   "outputs": [],
   "source": [
    "# df_sim.nsmallest(10, 0)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "DLOuZrnMqkvL"
   },
   "outputs": [],
   "source": [
    "def get_recmovie_cnt_by_movieid(movieid, connection):\n",
    "  sql = 'select count(*) from ibmovie group by movieid having movieid=\\'%s\\'' % movieid\n",
    "  try:  \n",
    "    with connection.cursor() as cursor:\n",
    "      cout=cursor.execute(sql)\n",
    "      if cout == 0:\n",
    "        return 0\n",
    "      return cursor.fetchone()[0]  \n",
    "  except Exception as e:\n",
    "    print('exception@get_recmovie_by_movieid:'+str(e))\n",
    "    try:\n",
    "      connection.cursor().close()\n",
    "      connection.close()\n",
    "      print('closed')\n",
    "    except Exception as e1:\n",
    "      print('exception1@get_recmovie_by_movieid:' + str(e1))\n",
    "    connection = get_connection()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "yrvahS3UrUUA"
   },
   "outputs": [],
   "source": [
    "cnt = get_recmovie_cnt_by_movieid('10746430_', get_connection())\n",
    "print(cnt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "XfCBrL-8N0t3"
   },
   "outputs": [],
   "source": [
    "df.iloc[2850]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "I-h9C8SvN0AG"
   },
   "outputs": [],
   "source": [
    "start_index = 2849\n",
    "start_index = 2961"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "TZYb1opSbyAB"
   },
   "outputs": [],
   "source": [
    "def process_offline_compute_by_cosdis(rec_per_num):\n",
    "  rec_per_num = rec_per_num + 1\n",
    "  connection = get_connection()\n",
    "  for i in range(start_index, item_similarity.shape[0]):\n",
    "    df_sim_p = df_sim.nsmallest(rec_per_num, i)\n",
    "    df_sim_p = df_sim_p[i]\n",
    "    movie_id = df.iloc[i]['id']\n",
    "    recmovie_cnt = get_recmovie_cnt_by_movieid(movie_id, connection)\n",
    "    if recmovie_cnt == 200:\n",
    "      print('org...')\n",
    "      continue\n",
    "    print('new...')\n",
    "    time_now = datetime.datetime.now()\n",
    "    for rec_movie_item in df_sim_p.to_dict().items():\n",
    "      if rec_movie_item[0] != i:\n",
    "        rec_movie_index = rec_movie_item[0]\n",
    "        rec_movie_sim = rec_movie_item[1]\n",
    "        rec_movie_id = df.iloc[rec_movie_index]['id']\n",
    "        rec_movie_rat = df.iloc[rec_movie_index]['rat']\n",
    "        insert_one_ibmovie(id_=uuid.uuid4(), movieid=movie_id, recmovieid=rec_movie_id, recmovierat=rec_movie_rat, simrat=rec_movie_sim, time=time_now, enable='1', connection=connection)\n",
    "    connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "XyDv1CbL0Kw8"
   },
   "outputs": [],
   "source": [
    "process_offline_compute_by_cosdis(rec_per_num=200)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "Fdnm4mx8d-nK"
   },
   "outputs": [],
   "source": [
    "# df_sim_3_dict = df_sim_3[4].to_dict()\n",
    "# for i in df_sim_3_dict.items():\n",
    "#   print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 168
    },
    "colab_type": "code",
    "collapsed": false,
    "executionInfo": {
     "elapsed": 1148,
     "status": "error",
     "timestamp": 1545827631444,
     "user": {
      "displayName": "Cheng Gavin",
      "photoUrl": "",
      "userId": "16557339152332771497"
     },
     "user_tz": -480
    },
    "id": "4hfc0klZurPx",
    "outputId": "aa337372-dc0f-424d-87fa-f2a9d39442fd"
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "ignored",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-1-d7b4a88031cb>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mpickle\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'pickle' is not defined"
     ]
    }
   ],
   "source": [
    "pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "UxcwbK11YPVU"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "yCjdKv03YpUm"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "accelerator": "GPU",
  "anaconda-cloud": {},
  "colab": {
   "collapsed_sections": [],
   "name": "3_recbycontent_v2.ipynb",
   "provenance": [],
   "version": "0.3.2"
  },
  "kernelspec": {
   "display_name": "Python [default]",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
